﻿using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace ScriptIT
{
    class Databases: IEnumerable
    {
        private List<Database> m_colDatabases = new List<Database>();

        public Databases()
        {
            this.Refresh();
        }
        
        #region Exposed Public Methods
		public Database Create()
        {
            Database oDB = new Database();
            Microsoft.Data.ConnectionUI.DataConnectionDialog _dialog = new Microsoft.Data.ConnectionUI.DataConnectionDialog();

            //Microsoft.Data.ConnectionUI.DataSource.AddStandardDataSources(_dialog);
            _dialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource);
            _dialog.SetSelectedDataProvider(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource, Microsoft.Data.ConnectionUI.DataProvider.SqlDataProvider);
            Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(_dialog);
            System.Windows.Forms.MessageBox.Show( _dialog.DisplayConnectionString);
            oDB.ConnectionString = _dialog.ConnectionString;
            oDB.Name = GetDBName(oDB);
            if (oDB.Name == "@")
                return null;

            SaveDBtoConfig(oDB);
            return oDB;
        }

        public void Add(Database oDB)
        {
            SaveDBtoConfig(oDB);
        }

        public void Remove(Database oDB)
        {
            RemoveDBFromConfig(oDB.Key);
        }

        public void Remove(string DBName)
        {
        }

        private void RemoveDBFromConfig(string dbKey)
        {
            XmlNode xToRemove = null;
            string configFileName = System.Environment.CurrentDirectory + "\\" + "Databases.Config";
            if (File.Exists(configFileName))
            {
                XmlDocument xdocdatabases = new XmlDocument();
                xdocdatabases.Load(configFileName);
                XmlNode xdatabases = xdocdatabases.SelectSingleNode("databases");
                if (xdatabases.ChildNodes.Count > 0)
                {
                    foreach (XmlNode xdatabase in xdatabases)
                    {
                        if (xdatabase.Attributes["key"].Value.ToString() == dbKey)
                        {
                            xToRemove = xdatabase;
                            break;
                        }
                    }
                    if (xToRemove != null)
                    {
                        xdatabases.RemoveChild(xToRemove);
                        xdocdatabases.Save(configFileName);
                    }
                        
                }
            }
        }

        private void SaveDBtoConfig(Database oDB)
        {
            XmlDocument config = new XmlDocument();
            string configFileName = System.Environment.CurrentDirectory + "\\" + "Databases.Config";
            if (!File.Exists(configFileName))
            {
                XmlNode mainNode = config.CreateXmlDeclaration("1.0", "UTF-8", null);
                config.AppendChild(mainNode);

                XmlNode configNode = config.CreateElement("databases");
                XmlAttribute Version = config.CreateAttribute("version");
                Version.Value = "1.0";
                configNode.Attributes.Append(Version);
                
                config.AppendChild(configNode);
                config.Save(configFileName);
            }
            else
                config.Load(configFileName);

            XmlNode tableNode = config.SelectSingleNode("databases");
            XmlAttribute Key = config.CreateAttribute("key");
            XmlAttribute Name = config.CreateAttribute("name");
            XmlAttribute ConnectionString = config.CreateAttribute("connectionString");
            XmlAttribute Description = config.CreateAttribute("description");

            Name.Value = oDB.Name;
            ConnectionString.Value = oDB.ConnectionString;
            Description.Value = oDB.Description;

            XmlNode xNewNode = config.CreateNode(XmlNodeType.Element, "database", null);
            xNewNode.Attributes.Append(Key);
            xNewNode.Attributes.Append(Name);
            xNewNode.Attributes.Append(ConnectionString);
            xNewNode.Attributes.Append(Description);

            tableNode.AppendChild(xNewNode);
            config.Save(configFileName);
            m_colDatabases.Add(oDB);

        }
        public Database Edit(Database oDatabase)
        {
            return oDatabase;
        }

        public List<Database> AsList
        {
            get { return m_colDatabases; }
        }
	    #endregion    
 
        private string GetDBName(Database oDB)
        {
            string sDBName = string.Empty;
            string sSrvName = string.Empty;
            string[] sStr = oDB.ConnectionString.Split(Convert.ToChar(";"));
            foreach (string sSubStr in sStr)
            {
                if (sSubStr.Contains("Data Source"))
                {
                    sSrvName = sSubStr.Substring(sSubStr.IndexOf(Convert.ToChar("=")) + 1);
                }
                if (sSubStr.Contains("Initial Catalog"))
                {
                    sDBName = sSubStr.Substring(sSubStr.IndexOf(Convert.ToChar("=")) + 1);
                }
            }
            return sDBName + "@" + sSrvName;
        }

        private class DatabaseEnumerator : IEnumerator
        {
            #region local vars

            private int m_iPos = -1;
            private Databases objDataDatabases;

            #endregion

            #region constructors

            public DatabaseEnumerator(Databases objDataEntries)
            {
                this.objDataDatabases = objDataEntries;
            }

            #endregion

            #region attrributes

            public object Current
            {
                get
                {
                    return
                        objDataDatabases.m_colDatabases[m_iPos];
                }
            }

            #endregion

            #region public operations

            public bool MoveNext()
            {
                if (m_iPos < objDataDatabases.m_colDatabases.Count - 1)
                {
                    m_iPos++;
                    return true;
                }
                else
                    return false;
            }

            public void Reset()
            {
                m_iPos = -1;
            }

            #endregion

        }

        #region Public Methods

        public IEnumerator GetEnumerator()
        {            
            return new DatabaseEnumerator(this);
        }

        public List<Database> Refresh() 
        {
            string configFileName = System.Environment.CurrentDirectory + "\\" + "Databases.Config";
            if (File.Exists(configFileName))
            {
                XmlDocument xdocdatabases = new XmlDocument();
                xdocdatabases.Load(configFileName);
                XmlNode xdatabases = xdocdatabases.SelectSingleNode("databases");
                if (xdatabases.ChildNodes.Count > 0)
                {
                    foreach (XmlNode xdatabase in xdatabases)
                    {
                        Database oDBItem = new Database();
                        oDBItem.Key = xdatabase.Attributes["key"].Value;
                        oDBItem.Name = xdatabase.Attributes["name"].Value;
                        oDBItem.Description = xdatabase.Attributes["connectionString"].Value;
                        oDBItem.ConnectionString = xdatabase.Attributes["description"].Value;
                        m_colDatabases.Add(oDBItem);
                    }
                }
            }
            return m_colDatabases;
        }

        public int Count()
        {
            return m_colDatabases.Count;
        }
        #endregion


    }
    [Serializable()]
    public class Database
    {
        string _key = DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString();
        string _Name = string.Empty;
        string _Description = string.Empty;
        string _ConnectionString = string.Empty;
        SqlConnection _connection = null;

        public override string ToString()
        {
            return _Name;
        }
        [System.Xml.Serialization.XmlElementAttribute("DBName")]
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("Description")]
        public string Description
        {
            get { return _Description; }
            set { _Description = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("ConnectionString")]
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("Key")]
        public string Key
        {
            get { return _key; }
            set { _key = value; }
        }

        [System.Xml.Serialization.XmlIgnore]
        public SqlConnection Connection
        {
            get
            {
                return _connection;
            }
        }
        private void OpenConnection()
        {
            if (_connection == null)
            {
                _connection = new SqlConnection(_ConnectionString);
                _connection.Open();
            }
        }
        public DataSet ExecuteSP(string spname)
        {
            DataSet dtRetValue = new DataSet();
            
            OpenConnection();
            SqlCommand spCommand = new SqlCommand(spname, _connection);
            spCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sqlData = new SqlDataAdapter(spCommand);
            sqlData.Fill(dtRetValue);

            return dtRetValue;
        }

        public DataSet ExecuteSP(SqlCommand command)
        {
            DataSet dtRetValue = new DataSet();
            OpenConnection();

            SqlDataAdapter sqlData = new SqlDataAdapter(command);
            sqlData.Fill(dtRetValue);

            return dtRetValue;
        }

        public DataSet ExecuteSP(string spname, SqlParameter[] parameters)
        {
            DataSet dtRetValue = new DataSet();

            OpenConnection();
            SqlCommand spCommand = new SqlCommand(spname, _connection);
            spCommand.CommandType = CommandType.StoredProcedure;
            spCommand.Parameters.AddRange(parameters);
            SqlDataAdapter sqlData = new SqlDataAdapter(spCommand);
            sqlData.Fill(dtRetValue);

            return dtRetValue;
        }
        public DataSet ExecuteDataset(string sSQL)
        {
            DataSet dtRetValue = new DataSet();

            OpenConnection();

            SqlCommand selectSqlCommand = new SqlCommand(sSQL, _connection);
            selectSqlCommand.CommandType = CommandType.Text;
            SqlDataAdapter sqlData = new SqlDataAdapter(selectSqlCommand);
            sqlData.Fill(dtRetValue);

            return dtRetValue;
        }
        public DataSet ExecuteDataset(SqlCommand command)
        {
            DataSet dtRetValue = new DataSet();

            OpenConnection();
            command.Connection = _connection;
            SqlDataAdapter sqlData = new SqlDataAdapter(command);
            sqlData.Fill(dtRetValue);

            return dtRetValue;
        }
    }
}
